import java.sql.*;

class DBConnection{
    String driver="com.mysql.cj.jdbc.Driver";
    String url="jdbc:mysql://localhost:3306/ssct?useUnicode=true&characterEncoding=UTF8";
    String user="root";
    String password="123456";
    Connection coon=null;
    public DBConnection(){
        try{
            Class.forName(driver);
            coon= DriverManager.getConnection(url,user,password);
            if(!coon.isClosed()){
                System.out.println("成功连接数据库！");
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    public void close(){
        try{
            this.coon.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }
    //      插入
    public void add(String Student_id,String Sname,int Sage,String Ssex,String Dept_id,String Sclass){
        String sql="insert into student(Student_id,Sname,Sage,Ssex,Dept_id,Sclass) values(?,?,?,?,?,?)";
        try{
            PreparedStatement preStmt= this.coon.prepareStatement(sql);
            preStmt.setString(1, Student_id);
            preStmt.setString(2, Sname);
            preStmt.setInt(3, Sage);
            preStmt.setString(4, Ssex);
            preStmt.setString(5, Dept_id);
            preStmt.setString(6, Sclass);

            preStmt.executeUpdate();
            System.out.println("插入数据成功！");
            preStmt.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    //    查询
    public void select(String Student_id){
        String sql="select * from student where Student_ID='"+Student_id+"'";
        try{
            Statement stmt= this.coon.createStatement();
            ResultSet rs= stmt.executeQuery(sql);
            System.out.println("--------------------------------");
            System.out.println("Student_id"+"\t"+"Sname"+"\t"+"Sage"+"\t"+"Ssex"+"\t"+"Dept_id"+"\t"+"Sclass");
            System.out.println("--------------------------------");
            while(rs.next()){
                String sid=rs.getString("Student_id");
                String name=rs.getString("Sname");
                int sage=rs.getInt("Sage");
                String ssex=rs.getString("Ssex");
                String did=rs.getString("Dept_id");
                String sclass=rs.getString("Sclass");
                System.out.println(sid+"\t"+name+"\t"+sage+"\t"+ssex+"\t"+did+"\t"+sclass);
            }
            stmt.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    //    更改数据
    public void update(String id,String Sname, int Sage, String Ssex, String Dept_id, String Sclass){
        String sql="update student set Sname = ?, Sage = ?, Ssex = ?, Dept_id = ?, Sclass = ? where Student_id = ?";
//        String sql="update usrInfo set age="+age+" where username='"+name+"'";
        try{
            PreparedStatement prestmt=(PreparedStatement)this.coon.prepareStatement(sql);
            prestmt.setString(1, Sname);
            prestmt.setInt(2, Sage);
            prestmt.setString(3,Ssex);
            prestmt.setString(4,Dept_id);
            prestmt.setString(5,Sclass);
            prestmt.setString(6,id);
            prestmt.executeUpdate();
            System.out.println("更改数据成功！");
            prestmt.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }
    //    删除数据
    public void del(String Student_id){
        String sql="delete from student where Sname=?";
        try{
            PreparedStatement prestmt=(PreparedStatement)this.coon.prepareStatement(sql);
            prestmt.setString(1, Student_id);
            prestmt.executeUpdate();
            System.out.println("删除数据成功！");
            prestmt.close();
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}